Wie met databronnen en software van Amerikaanse makelij te maken heeft – van Microsoft Office tot vi-UNIX – loopt vanzelf tegen het probleem van de datumnotatie aan. Soms is het makkelijk aan te passen, soms niet. Zo kan een bepaalde functie hardnekkig terugkeren naar de ‘oer’-datumnotatie mm-dd-yyyy. Hoe zit dit met Tableau software? Is het mogelijk om een Amerikaanse en Europese datum format of datumnotatie samen te voegen?
Dit is het eerste deel van het blog, het tweede deel (over Tableau Prep) is hier te vinden.
De Amerikaanse en Europese datumnotatie of datum formats
Stel een bedrijf dat hout importeert uit verschillende EU-landen en de VS ontvangt de onderstaande twee bestanden met transportgegevens. De linker tabel bevat gegevens met de Europese datumnotatie (dag-maand-jaar of dd-mm-yyyy). De rechter tabel de Amerikaanse notatie (maand-dag-jaar of mm-dd-yyyy). Voor het totaaloverzicht moet dit in één worksheet komen zonder dat er in het worksheet zelf met calculated fields gepuzzeld moet worden. (Het is namelijk handig als elke Tableau-gebruiker dezelfde kolomnamen gebruikt en in het algemeen dezelfde gegevens ontvangt).
Oplossingen Tableau Desktop
In Tableau Desktop kan via de functie union (voor meer informatie over unions, zie deze link). Voor dit voorbeeld is gebruik gemaakt van een Excel-bestand waarin de Europese en Amerikaanse lijst op verschillende tabbladen staan. Als u contact legt met de bron en één van de tabbladen het veld insleept ziet het er als volgt uit:
Tableau Desktop herkent de kolommen met de data automatisch. Door de tabel/het tabblad ‘USA imports’ in het veld te slepen kan een union gemaakt worden. Het resultaat in het Data Grid ziet u hieronder:
Tableau herkent de waarden niet, omdat de kolommen met de Amerikaanse notatie niet als datum maar als tekst (string) gezien worden:
Oplossing A – de datum hervormen of omvormen
Om in de Data Source Page de Union goed uit te voeren zult u eerst de kolom ‘Shipping date’ moeten aanpassen, dit kan via een calculated field. Door op het pijltje/driehoekje te klikken dat verschijnt als u met de muis over de kolomkop zweeft kunt u calculated field selecteren (zie hieronder links). Vul vervolgens de formule in zoals rechtsonder:
De formule bestaat uit de volgende delen, als voorbeeld pakken we de datum uit de eerste regel: 12-06-2021 op zijn Amerikaans. Oftewel: 6 december 2021 volgens onze jaartelling.
- I. MID ([Shipping date],4,2) zoekt de tekens in een string aan de hand van een opgegeven startpunt en lengte. De functie is letterlijk: MID(string, start, length (length is optioneel). In de voorbeeldregel (12-06-2021) is het vierde teken ‘0’ en is de lengte van de totale tekst twee tekens, dus is het resultaat ’06’.
- II. LEFT([Shipping date],2), zoekt de tekens in een string vanaf links en telt het aantal tekens aan de hand van een ingevoerd getal. De functie is letterlijk: LEFT (string, num_chars). In de voorbeeldregel (12-06-2021) is hierdoor het eindresultaat ’12’.
- III. RIGHT([Shipping date],4) zoekt de tekens in een string vanaf rechts en telt het aantal tekens aan de hand van het ingevoerde getal. De functie is letterlijk: RIGHT(string, num_chars). In de voorbeeldregel (16-06-2021) is het eindresultaat hierdoor ‘2021’.
De delen “+’-‘+” zijn bedoeld om de streepjes tussen de datum-delen terug te krijgen. Uiteindelijk is het resultaat een string. Via een calculatie kunt u dit aanpassen, maar er is een makkelijkere manier.
Klik op de tekst ‘=Abc’ boven in de kolom ‘Shipping date EU’ en selecteer ‘Date’.
NB
Als u wilt weten hoe dat er in formulevorm uitziet, klik dan op het pijltje/de driehoek rechtsboven in de kolom. Klik vervolgens op ‘edit’. De formule is dan:
- DATE(DATEPARSE ( “dd-MM-yyyy”, MID([Shipping date],4,2)+’-‘+LEFT([Shipping date],2)+’-‘+RIGHT([Shipping date],4) ))
Technisch werken de functies zo: DATE (Expressions) en DATEPARSE( format, string), DATEPARSE maakt een DATETIME aan, in dit voorbeeld werk ik liever met DATE.
Een korte toelichting op de functies:
- DATE(expression) verandert een waarde tussen de haakjes in een datum, die waarde moet dan wel een number, string of date expression zijn.
- DATEPARSE(format,string), verandert een string naar een specifiek datum-format, bijvoorbeeld ‘dd-MM-yyyy’ uit het voorbeeld zet het Amerikaanse ‘6 december 2021′ om naar het Europese ’06-12-2021’.
Oplossing B – De Union mogelijk maken
Nu u de datumvelden uit het Amerikaanse bestand recht gebreid heeft kunt u de union maken, maar er zit een addertje onder het gras….
Als we de kolommen met afmetingen willen samenvoegen kan dat door op de eerste kolom te klikken op ‘Ctrl’ (bij Mac ‘command’) te drukken en op de volgende kolom te klikken. Vervolgens klikt u op het pijltje/driehoekje rechtsboven en selecteert ‘merge mismatched columns’.
Dit werkt helaas niet als ‘Shipping date EU’ en ‘Ontvangstdatum’ geselecteerd worden, bij een calculated field is deze union-‘truc’ niet mogelijk.
Gelukkig is Tableau niet voor één gat te vangen, via een nieuw calculated field met de volgende berekeningen kan het:
- DATE(DATEPARSE ( “yyyy-MM-dd”, IFNULL(STR([Verzenddatum]),STR([Shipping date EU])) ))
Wat gebeurt hier? De functies DATE en DATEPARSE zijn al toegelicht, maar wat doen de functies IFNULL en STR? Met IFNULL(expr 1, expr2) zorgt de formule ervoor dat bij een lege waarde (NULL) in expr1 de waarde uit expr2 gebruikt wordt. In de afbeeldingen is te zien dat de er geen overlap zit in de waarden. Met andere woorden, door de union-mismatch is er een NULL in de cel als de kolomnaam niet overeenkomt. Door IFNULL worden de twee kolommen die in expr1 en expr2 genoemd worden samengevoegd.
STR is nodig om met IFNULL te werken, aangezien het datum format niet werkt met die functie. De functie STR maakt van een waarde een string value.
Als de halfvolle (of halflege) kolommen verborgen zijn ziet het eindresultaat er als volgt uit:
Het kan natuurlijk dat u liever heeft dat de data al netjes en in één kolom in Desktop binnenkomt. Die oplossing is hier te vinden, in het tweede deel (over het Amerikaans datum format samenvoegen).
Bronnen en extra informatie
Een korte uitleg over Union is hier te vinden of op de site van Tableau zelf of een compactere uitleg hier.
Voor meer uitleg over de string-functions, zie de Tableau-site of deze Youtube filmpjes over de functie ‘MID’ en de functies LEFT en RIGHT.
Wilt u meer weten over de functies DATE en DATEPARSE, klik dan hier voor de Tableau-site of hier voor een korter overzicht.
Dit blog is (deels) gebaseerd op deze post op community.tableau.com van Michael Ye.
( Photo by Sinjin Thomas on Unsplash)